const seq = require('./seq')
const { QueryTypes } = require('sequelize');
const moment = require('moment');

class permission {

  async delOprationByID(id) {
    const currentTime = moment().format('YYYY-MM-DD');
    const sql = `update oprations set isdelete=1 ,updatedtime=? where id = ${id}`
    //console.log('sql::',sql)
    return await seq.query(sql, { 
      replacements: [currentTime] ,
      type: QueryTypes.UPDATE 
    })
  }
  
  async updateOpByID(data) {
    let {id,name,opname,opRoles,isNew,permid} =data
    //console.log('data:',data)
    let sql = ``
    if(isNew)
    {
      sql=`insert into oprations (name,opname,permid) values (?,?,?)`
      const ret = await seq.query(sql,
        {
          replacements: [name,opname,permid],
          type: QueryTypes.INSERT
        }
      )
      //console.log('insert:',ret) 
      if(ret && ret.length>0)
      {
        id=ret[0]
      }
      else{
        ctx.app.emit('error','按钮功能插入失败',ctx)  
      }
    }
    else{
      sql = `update oprations set name = ?,opname=? where id=?`
      const results = await seq.query(sql,
        {
          replacements: [name,opname,id],
            type: QueryTypes.UPDATE
        }) 
    }
    
    let sqlpermroles=''
    if(!isNew)
    {
      sqlpermroles=`delete from oproles where opid=${id}` 
      await seq.query(sqlpermroles,
      {
        type: QueryTypes.DELETE
      }
      )
    }
    if (opRoles && opRoles.length > 0){
      let sqlvalues=''
      for(let i=0;i<opRoles.length;i++)
      {
        if(i==opRoles.length -1 ){
          sqlvalues+= `(${id},${opRoles[i]})`
        }
        else{
          sqlvalues+= `(${id},${opRoles[i]}),`
        }
      }
      sqlpermroles = `insert into oproles (opid,roleid) VALUES `
      sqlpermroles = sqlpermroles + sqlvalues
      await seq.query(sqlpermroles,
        {
          type: QueryTypes.INSERT
        }
      )
    }
    return id
  }


  async getAll() {
    const sql = `SELECT *
                FROM permission 
                where  isdelete=0`
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }) 
    return results
  }

  async  getopsByPermID(permid,flag) {
    let sql = ``
    if(flag===-1){
      sql = `select opr.*,oproles.roleid from oprations opr
            left join transoprolestoarry oproles
            on  opr.id=oproles.id
            where isdelete=0`
    }
    else{
      sql = `select opr.*,oproles.roleid from oprations opr
            left join transoprolestoarry oproles
            on  opr.id=oproles.id
            where opr.permid=${permid} and isdelete=0`
    }
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }) 
    return results
  }

  async  getopsRolesByOpID(opID) {
    const  sql = `select * from oproles where opid = ${ipID} and isdelete=0`    
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }) 
    return results
  }

  async getAllpermRoles() {
    const sql = `select pr.*,p.name as permname,r.name as rolename from permroles as pr
                left join  permission as p on pr.permid=p.id
                left join  role as r on pr.roleid=r.id`
    const results = await seq.query(sql,
      {
        type: QueryTypes.SELECT
      }) 
    return results
  }

  async savePermRoles(data) {
    let {id,permname,roles,newMenu,name} = data
    let sqlPerm=''
    const currentTime = moment().format('YYYY-MM-DD HH:mm:ss');
    if(newMenu && newMenu===1)
    {
      sqlPerm = `insert into permission 
            set name=?,permname=?,updateTime=?,pid=1,flag=0`
      const ret = await seq.query(sqlPerm,
        {
          replacements: [name,permname,currentTime],
          type: QueryTypes.INSERT
        }
      )
      id = ret[0]
      //console.log('RET',ret)
    }
    else{
      sqlPerm = `update  permission 
            set name=?,permname=?,updateTime=?,pid=1,flag=0 where id=${id}` 
      await seq.query(sqlPerm,
        {
          replacements: [name,permname,currentTime],
          type: QueryTypes.UPDATE
        }
      )
    }
    
    let sqlpermroles=''
    if(!(newMenu && newMenu===1))
    {
      sqlpermroles=`delete from permroles where permid=${id}` 
      await seq.query(sqlpermroles,
      {
        type: QueryTypes.DELETE
      }
      )
    }    
    //`INSERT INTO student (name, age, gender) VALUES 
    //('Alice', 18, 'female'), ('Bob', 20, 'male'), ('Charlie', 19, 'male')`
    if (roles && roles.length > 0){
      let sqlvalues=''
      for(let i=0;i<roles.length;i++)
      {
        if(i==roles.length -1 ){
          sqlvalues+= `(${id},${roles[i]})`
        }
        else{
          sqlvalues+= `(${id},${roles[i]}),`
        }
      }
      sqlpermroles = `insert into permroles (permid,roleid) VALUES `
      sqlpermroles = sqlpermroles + sqlvalues
      await seq.query(sqlpermroles,
        {
          type: QueryTypes.INSERT
        }
      )
    }
    return id
  }
}

module.exports = new permission()